| Tanel Poder Masterclass (UK) - Advanced Oracle SQL Tuning |
|
|
From Tuesday, 1. June 2010 - 09:00
To Thursday, 3. June 2010 - 17:00
Every day |
|
|
|
Advanced Oracle SQL TuningTanel will be in 2010 in
* London (UK) - 1,2 and 3 June 2010 (click here to register)
* Utrecht (NL) - 7,8 and 9 June 2010 (click here to register)
* Brussel (BE) - not decided yet
Seminar overviewThis 3-day Masterclass is entirely about making Oracle SQL execution run faster, understanding the root causes of SQL performance problems and Cost Based Optimizer misbehavior.
You will learn the full range of techniques for SQL tuning starting from optimizer stats placement strategy all the way to advanced topics such as comprehensive SQL hinting and CBO’s estimated row count adjustments.
We start from the low level fundamentals, explaining how exactly Oracle SQL plan execution works, what are the main factors affecting query performance and how to systematically diagnose and fix SQL performance problems.
Some SQL performance problems cannot be cured by just an easy change to application code or optimizer stats, so a significant part of this seminar also concentrates on good database design and indexing practices.
Finally, this class is not about a limited list of "tips and tricks", instead it will give you the knowledge and tools for systematic tuning and troubleshooting SQL of any complexity.
Seminar Duration and DetailsSeminar duration: 3 days
Audience: Senior DBAs, senior developers, database designers
Skill level: Intermediate to Advanced
Objectives
· Gain deep understanding of SQL plan execution flow
· Know what and how to change to get efficient execution plans
· Be able to manually control SQL execution plans and guarantee their stability
· Be able to systematically index tables for access performance
· Help CBO to find good plans, to keep the manual tuning to minimum
After this class you won’t need to memorize every single SQL performance or optimizer problem out there, instead you will be able to systematically work out the problem root causes yourself!
Non-objectives
· This is not a beginners “How to write SQL” class (although we will talk about rewriting SQL for optimization purposes)
· This is not a database/instance tuning class (Visit tech.e2sn.com for other classes)
You will learn the following…
1) Understanding SQL Plan Execution· Deep understanding of how exactly Oracle executes SQL statements, both SELECT and DML statements
· Understand the fundamental factors affecting query execution performance
· How to use a systematic approach for tuning SQL of any complexity
2) Controlling SQL Execution Plans· How to make a SQL execution plan do exactly what you want
· How to achieve SQL performance and plan stability
· Understand why SQL execution plans unexpectedly change even when nothing else has apparently changed – and what to do about it
· Understand why can the SQL performance suddenly drop, when nothing, even the execution plan itself has not changed – and what to about it
· How to optimize SQL when you cannot change the code?
3) Accessing Data Efficiently· Systematic indexing – which index type to use, when and how
· How to decide which columns and in which order to index - without trial and error
· Understanding index-based data retrieval overhead
· Understanding index maintenance overhead
· Identifying best candidates for index-organized tables
4) Helping the Cost Based Optimizer to Find Good Plans· Understand what exactly is the Optimizer Cost, which input data is used for cost calculations and where does this number matter
· How to configure Oracle, statistics and CBO to keep the need for manual SQL tuning to the minimum
· How to troubleshoot CBO Cost and row count misestimates and bugs
Seminar content technical details
1) Understanding Execution PlansIn this part you will learn how exactly Oracle executes SQL execution plans. Instead of describing on PowerPoint sides how things might “logically” happen in Oracle, we will take a deep dive into the exact sequence of how Oracle “physically” executes the row sources in any execution plan and how does the data flow during plan execution.
You will learn how access path operators fetch rows from database segments and how the rows are passed through the execution plan tree throughout the lifecycle of SQL execution. You will also learn how row joining, filtering and comparison works, such as nested loop/hash joins and also the frequently seen FILTER operation.
The understanding of how SQL plan execution physically works will become a strong and required foundation for any systematic SQL tuning and troubleshooting task.
Understanding SQL Plan Execution and Data Flow
· A detailed look into how SQL plan execution works in Oracle
o Walking the execution plan tree
o Understanding row source operators
o Buffering vs. cascading row sources
· Getting execution plans from the right source
· Reading execution plans the right way
· Identifying where the response time is spent
· Profiling SQL plan execution
o V$SQL_PLAN_STATISTICS_ALL
o SQL Trace STAT lines
o Real time SQL monitoring and V$SQL_MONITOR
o OS tools (os_explain, DTrace)
Predicates and data filtering
· Access vs. filter predicates
· INTERNAL_FUNCTION and pushed predicates
Understanding how the data joining and correlated lookups work
· Nested Loop Joins
· Hash Joins
o Partition-wise joins
· Merge Joins
· Correlated subquries and FILTER operation
· Connect By operation
Understanding the fundamental factors affecting query performance
· Row counts fetched from a row source (cardinality)
· Block visits and revisits
· Locality of data
· DML: Amount of data changed (redo/undo size)
· Lock and latch contention
· PX: Parallel execution data fetch and distribution mechanism
· Callouts from SQL execution plan (PL/SQL function calls from SQL)
Understanding why can SQL performance change even though the execution plan hasn’t changed
· Amount of work area memory allowed to use by work area manager
· Hash multipass join decisions
· Dynamic hash input swapping
· Number of parallel slaves currently available
· Runtime prefetching decisions
o Nested loop prefetching
o Direct read segment scan prefetching
o Index multiblock read prefetching
2) Controlling SQL Execution PlansIn this part you will learn how to make a SQL execution plan do exactly what you want it to do. We will look into the full range of techniques such as query rewriting, full hinting, stored outlines, SQL profiles, adjusting row count estimates and passing optimizer parameters into individual statements.
We will start from identifying important prerequisites to any systematic SQL tuning, such as understanding the amount and “shape” of data accessed and what is the SQL statement supposed to do. This allows you to understand what would be the ideal execution order and hierarchy – which you can then achieve with the techniques covered next in this section.
As no SQL tuning technique is appropriate in all situations, we will also look into how to pick the right technique for fixing the right problem and common SQL optimization pitfalls.
Three main properties of an execution plan - data retrieval
· Join order (execution order and hierarchy)
· Join methods
· Access paths
Controlling execution plans
· Picking the right join order
· Picking the right join methods
· Picking the right access paths
Understanding query transformations
· Heuristic query transformations
· Cost based query transformations (CBQT)
· Simple view merging
· Complex view merging
· Subquery unnesting, antijoin, semijoin conversions
o IN, NOT IN, EXISTS, NOT EXISTS
· Join elimination transformations
· Predicate move-around
· Others…
Controlling work area operations and reducing memory usage
· Sorting, grouping and other work area operations
Making the SQL execution plan do exactly what you want – and always
· Hinting the right way
· Hinting pitfalls and what to avoid
· Using SQL profiles
· Adjusting schema object statistics
· Using stored outlines
· Strategy and techniques for achieving execution plan stability 3) Accessing Data EfficientlyIn this part we will go deep into efficient ways of retrieving data from the database (accessing rows using table, index access paths etc). We will look into how to systematically detect inefficient data access even if nobody is complaining (yet) about performance. A large majority of this section focuses on indexes, how they work, how to use them, how to avoid using them.
After completing this part, you will be able to systematically work out which columns and in which order would give the best results for a workload – there’s no need for repetitive trial and error!
Mantras of efficient SQL execution
· Don’t request too much work!
o Let the database know about your intentions
o Ask only the columns you need
o Ask only the amount of rows you need
o Ask only the detail you need
o Help Oracle to know the data
· Don’t do too much work! (Simple, huh? J)
o Retrieve data efficiently – reduce number of IO-s per row retrieved by access path
o Filter data early – eliminate non-matching rows close to the access path
o Process retrieved data efficiently – avoid heavy loops and iterations
Achieving efficient data access
· Understanding the data retrieval overhead of different access paths
· Identifying significant “throw-away” of retrieved data
· Using the right access paths for retrieving data
· Filtering data early
Systematic indexing
· How to know in advance which indexes would help with data retrieval performance
· Calculating the best column order for multicolumn indexes
· Understanding lookup overhead of indexes
· Understanding why the index clustering factor matters so much
· Understanding index maintenance overhead
Indexing strategy for high-concurrency updates and insert distribution of “right hand” indexes (and RAC)
· Reverse-key indexes
· Index column prefixing with instance ID
· Index column prefixing with a deterministic function
Best practices for bitmap indexing
· Avoiding heavy DML on bitmap indexes
· Achieving efficient bitmap index combining
· Achieving star transformation in star schemas
4) Helping Cost Based Optimizer to Find Good PlansWhile the past parts of this seminar give you the knowledge of how to optimize SQL and control execution plans yourself, it is not realistic to manually tune every SQL statement yourself. This is exactly why the Cost Based Optimizer exists.
Unfortunately Oracle’s Cost Based Optimization process is not perfect; in some cases because of the Optimizer’s design limitations, sometimes bugs, but often because incorrectly configured optimizer environment and statistics collection setup.
This part of the seminar provides you the required understanding of CBO internals and SQL plan execution’s cost estimation process. Armed with this knowledge you will learn how to put together a strategy for configuring Cost Based Optimizer environment and statistics collection correctly. We will look into different requirements by different workloads (such as OLTP vs. DSS systems).
Additionally we will look into how to systematically work out the root causes of CBO row count and cost misestimates resulting in plan instability and bad performance.
Understanding CBO inputs
· SQL text
o SQL of views accessed
o Columns selected
o Predicate conditions
o Hints in SQL statements
· Table, index structure
o Table columns, indexed columns and indexed column order
o Table/index physical structure (cluster, index-organized table etc)
o Partitioning structure and keys
· Table constraints
· Optimizer statistics, System statistics
· Session parameters (optimizer environment)
· Work area size used (pga_aggregate_target)
Understanding CBO outputs
· What is the Optimizer Cost?
· How does CBO try to come up with efficient execution plan?
· Common reasons for CBOs wrong decisions
· Avoiding underestimating row counts
Understanding optimizer’s by-design limitations
· Handling “out of range” predicate conditions
· Handling data skew
· Handling imbalanced filter and join correlations
· Using dynamic sampling for join-correlation aware statistics
· Costing PL/SQL functions used in SQL statements
Good CBO configuration practices
· Setting up optimizer for majority of queries
· Stopping fine-tuning optimizer parameters
Good statistics gathering practices
· How to gather realistic system statistics
· Where to use histograms and where definitely not to do so
· Freezing statistics
· Adjusting statistics
· Copying statistics
· 11g improvements
Advanced CBO troubleshooting
· Using the optimizer trace (event 10053) for optimizer problem troubleshooting
· Troubleshooting heavy cardinality misestimates
· Troubleshooting cost based query transformation problems
Advanced Oracle SQL Tuning seminar by Tanel Poder
http://blog.tanelpoder.com | http://tech.e2sn.com |
Location: London (UK)
Contact: Annette Herruer - 0641788142 |
Back
|
